package org.example.kg_back.mapper;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.example.kg_back.PoJo.entity.Aftn;

import java.util.List;

@Mapper
public interface AftnMapper extends BaseMapper<Aftn> {


	//获取机场飞机起飞的数量
	@Select("select kg_airport.AIRPORT_CNAME as airCname,count(*) as adepCount\n" +
			"from kg_airport INNER JOIN kg_aftn on kg_airport.AIRPORT_CODE4 = kg_aftn.adep\n" +
			"GROUP BY kg_aftn.adep;")
	List<Aftn> getAftnByAdep();

	//获取机场飞机降落的数量
	@Select("select kg_airport.AIRPORT_CNAME as airCname,count(*) as adesCount\n" +
			"from kg_airport INNER JOIN kg_aftn on kg_airport.AIRPORT_CODE4 = kg_aftn.ades\n" +
			"GROUP BY kg_aftn.ades")
	List<Aftn> getAftnByAdes();


	@Select("SELECT adep.airCname,adep.adepCount,ades.adesCount " +
			"FROM" +
			"( SELECT kg_airport.AIRPORT_CNAME AS airCname," +
			"count(*) AS adesCount " +
			"FROM " +
			"kg_airport " +
			"INNER JOIN kg_aftn ON kg_airport.AIRPORT_CODE4 = kg_aftn.ades " +
			"GROUP BY " +
			"kg_aftn.ades " +
			") AS ades right JOIN (" +
			"SELECT " +
			"kg_airport.AIRPORT_CNAME AS airCname, " +
			"count(*) AS adepCount " +
			"FROM " +
			"kg_airport " +
			"INNER JOIN kg_aftn ON kg_airport.AIRPORT_CODE4 = kg_aftn.adep " +
			"GROUP BY " +
			"kg_aftn.adep " +
			") AS adep ON ades.airCname = adep.airCname")
	List<Aftn> getAftnList();
}
